package com.huangpuweb.api.service.imp;

import com.huangpuweb.api.model.*;
import com.huangpuweb.api.model.dto.DianZhaoDianPaiDto;
import com.huangpuweb.api.service.ShiGongAnQuanApiService;
import com.huangpuweb.api.util.StringUtil;
import com.huangpuweb.api.util.StringUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Service
public class ShiGongAnQuanApiServiceImp extends BaseSysDataServiceImp implements ShiGongAnQuanApiService {


    @Override
    public Integer getBuildingAllNum() {
        String sql="select count(id) from sys_building_info";
        return queryInteger(sql,new Object[]{});
    }

    @Override
    public List<SysBuildingStreetInfo> getListSysBuildingStreetInfo() {
        String sql="select street,count(id) as buildingNum from sys_building_info group by street";
        return getEntryList(sql,new SysBuildingStreetInfoRowMapper(),new Object[]{});
    }

    @Override
    public List<SysBuildingInfo> getShiGongAnQuanBuildingList() {
        String sql="select * from sys_building_info where street=?";
        return getEntryList(sql,new SysBuildingInfoRowMapper(),new Object[]{"老西门街道"});
    }

    @Override
    public SysBuildingInfo getSysBuildingInfoById(String buildingId) {
        String sql="select * from sys_building_info where id=?";
        return getEntry(sql,new SysBuildingInfoRowMapper(),new Object[]{buildingId});
    }

    @Override
    public Integer listShiGongCountByStreet(String street) {
        String sql="select count(1) from sys_building_info where street=?";
        return queryInteger(sql,new Object[]{street});
    }

    @Override
    public Integer getDianZhaoDianpaiCount() {
        String sql="select count(1) from hp_dianzhaodianpai_info";
        return queryInteger(sql,new Object[]{});
    }

    @Override
    public List<HpDianZhaoDianPaiInfo> llistHpDianZhaoDianPaiInfo() {
        //sql优化
        //String sql="select * from hp_dianzhaodianpai_info ";
        String sql = "select id,name,address,wgs84Latitude,wgs84Longitude," +
                "street,length,height,connectmobile,connectname,imageurl,xingshi,liqiangjuli,biaogao,level,iconimageurl " +
                " from hp_dianzhaodianpai_info";
        return getEntryList(sql, new RowMapper<HpDianZhaoDianPaiInfo>() {
            @Override
            public HpDianZhaoDianPaiInfo mapRow(ResultSet rs, int i) throws SQLException {
                HpDianZhaoDianPaiInfo  lj= new HpDianZhaoDianPaiInfo();
                lj.setId(rs.getInt("id"));
                lj.setName(rs.getString("name"));
                lj.setAddress(rs.getString("address"));
                lj.setWgs84Latitude(rs.getString("wgs84Latitude"));
                lj.setWgs84Longitude(rs.getString("wgs84Longitude"));
                lj.setStreet(rs.getString("street"));
                lj.setLength(rs.getString("length"));
                lj.setHeight(rs.getString("height"));
                lj.setConnectmobile(rs.getString("connectmobile"));
                lj.setConnectname(rs.getString("connectname"));
                lj.setImageurl(rs.getString("imageurl"));
                lj.setXingshi(rs.getString("xingshi"));
                lj.setLiqiangjuli(rs.getString("liqiangjuli"));
                lj.setBiaogao(rs.getString("biaogao"));
                lj.setLevel(rs.getString("level"));
                lj.setIconimageurl(rs.getString("iconimageurl"));
                return lj;
            }
        },new Object[]{});
    }

    @Override
    public List<HpDianZhaoDianPaiInfo> llistHpDianZhaoDianPaiInfoTest() {
        //sql优化
        //String sql="select * from hp_dianzhaodianpai_info ";
        String sql = "select id,wgs84Latitude,wgs84Longitude" +
                " from hp_dianzhaodianpai_info";
        return getEntryList(sql, new RowMapper<HpDianZhaoDianPaiInfo>() {
            @Override
            public HpDianZhaoDianPaiInfo mapRow(ResultSet rs, int i) throws SQLException {
                HpDianZhaoDianPaiInfo  lj= new HpDianZhaoDianPaiInfo();
                lj.setId(rs.getInt("id"));
                lj.setWgs84Latitude(rs.getString("wgs84Latitude"));
                lj.setWgs84Longitude(rs.getString("wgs84Longitude"));
                return lj;
            }
        },new Object[]{});
    }

    @Override
    public List<HpDianZhaoDianPaiInfo> listDianZhaoDianPai(DianZhaoDianPaiDto dianZhaoDianPaiDto) {
        //sql优化
        //String sql="select * from hp_dianzhaodianpai_info ";
        String sql = "select id,wgs84Latitude,wgs84Longitude, level" +
                " from hp_dianzhaodianpai_info ";
        if(!StringUtils.isEmpty(dianZhaoDianPaiDto.getStreet())){
            sql = sql + " where street = '" + dianZhaoDianPaiDto.getStreet() + "'";
        }
        return getEntryList(sql, new RowMapper<HpDianZhaoDianPaiInfo>() {
            @Override
            public HpDianZhaoDianPaiInfo mapRow(ResultSet rs, int i) throws SQLException {
                HpDianZhaoDianPaiInfo  lj= new HpDianZhaoDianPaiInfo();
                lj.setId(rs.getInt("id"));
                lj.setWgs84Latitude(rs.getString("wgs84Latitude"));
                lj.setWgs84Longitude(rs.getString("wgs84Longitude"));
                lj.setLevel(rs.getString("level"));
                return lj;
            }
        },new Object[]{});
    }

    @Override
    public HpDianZhaoDianPaiInfo getDianZhaoDianPai(DianZhaoDianPaiDto dianZhaoDianPaiDto) {
        //sql优化
        //String sql="select * from hp_dianzhaodianpai_info ";
        String sql = "select id,name,address,wgs84Latitude,wgs84Longitude," +
                "street,length,height,connectmobile,connectname,imageurl,xingshi,liqiangjuli,biaogao,level,iconimageurl " +
                " from hp_dianzhaodianpai_info ";
        if(dianZhaoDianPaiDto.getId() !=  null){
            sql = sql + " where id = " + dianZhaoDianPaiDto.getId();
        }
        return getEntry(sql, new RowMapper<HpDianZhaoDianPaiInfo>() {
            @Override
            public HpDianZhaoDianPaiInfo mapRow(ResultSet rs, int i) throws SQLException {
                HpDianZhaoDianPaiInfo  lj= new HpDianZhaoDianPaiInfo();
                lj.setId(rs.getInt("id"));
                lj.setName(rs.getString("name"));
                lj.setAddress(rs.getString("address"));
                lj.setWgs84Latitude(rs.getString("wgs84Latitude"));
                lj.setWgs84Longitude(rs.getString("wgs84Longitude"));
                lj.setStreet(rs.getString("street"));
                lj.setLength(rs.getString("length"));
                lj.setHeight(rs.getString("height"));
                lj.setConnectmobile(rs.getString("connectmobile"));
                lj.setConnectname(rs.getString("connectname"));
                lj.setImageurl(rs.getString("imageurl"));
                lj.setXingshi(rs.getString("xingshi"));
                lj.setLiqiangjuli(rs.getString("liqiangjuli"));
                lj.setBiaogao(rs.getString("biaogao"));
                lj.setLevel(rs.getString("level"));
                lj.setIconimageurl(rs.getString("iconimageurl"));
                return lj;
            }
        },new Object[]{});
    }

    @Override
    public Integer getHpDianZhaoDianPaiStreetCount(String abc) {
        String sql="select count(1) from hp_dianzhaodianpai_info where street=?";
        return queryInteger(sql,new Object[]{abc});
    }

    @Override
    public List<HpDianZhaoDianPaiInfo> llistHpDianZhaoDianPaiInfoStreet(String street) {
       /* String sql="select * from hp_dianzhaodianpai_info where street=?";*/
        String sql = "select id,name,address,BaiduLatitude,BaiduLongitude,wgs84Latitude,wgs84Longitude," +
                "street,length,height,connectmobile,connectname,imageurl,xingshi,liqiangjuli,biaogao,level,iconimageurl " +
                " from hp_dianzhaodianpai_info where street=?";
        return getEntryList(sql,new HpDianZhaoDianPaiInfoRowMapper(),new Object[]{street});
    }

    @Override
    public List<HpDianZhaoDianPaiInfo> llistHpDianZhaoDianPaiInfoXiMenStreet(String str) {
        String sql="select * from hp_dianzhaodianpai_info_street where street=?";
        return getEntryList(sql,new HpDianZhaoDianPaiInfoRowMapper(),new Object[]{str});
    }

    @Override
    public List<HpDianZhaoDianPaiInfo> llistHpDianZhaoDianPaiInfoLevel(String level) {
        String sql="select * from hp_dianzhaodianpai_info where level=?";
        return getEntryList(sql,new HpDianZhaoDianPaiInfoRowMapper(),new Object[]{level});
    }

    @Override
    public List<HpDianZhaoDianPaiInfo> llistHpDianZhaoDianPaiInfoCondition(String street, String size, String heigthlevel, String xingshi, String level,String travelname) {
        String sql = "select * from hp_dianzhaodianpai_info  where id is not null   ";
        List<Object> params = new ArrayList<Object>();
        if(StringUtil.isNotEmpty(street)){
            sql += " and street=? ";
			params.add(street);
        }

        if(StringUtil.isNotEmpty(size)){
            sql += " and size=?";
            params.add(size);
        }


        if (StringUtil.isNotEmpty(heigthlevel)) {
            sql+=" and heigthlevel = ?";
            params.add(heigthlevel);
        }

        if (StringUtil.isNotEmpty(xingshi)) {
            sql+=" and xingshi = ?";
            params.add(xingshi);

        }

        if (StringUtil.isNotEmpty(level)) {
            sql+=" and level = ?";
            params.add(level);

        }

        if (StringUtil.isNotEmpty(travelname)) {
            sql+=" and travelname = ?";
            params.add(travelname);

        }




        return getEntryList(sql, new HpDianZhaoDianPaiInfoRowMapper(),params.toArray());
    }

    @Override
    public List<SysBuildingInfoStreet> listSysBuildingInfoStreet() {
        String sql="select * from sys_building_info_street";
        return getEntryList(sql,new SysBuildingInfoStreetRowMapper(),new Object[]{});
    }

    @Override
    public List<HpDianZhaoDianPaiTypeNameInfo> getDZDPSizeList(String street) {
        String sql="select size as name,count(1) as count from hp_dianzhaodianpai_info where street=? group by size ";
        return getEntryList(sql,new HpDianZhaoDianPaiTypeNameInfoRowMapper(),new Object[]{street});
    }

    @Override
    public List<HpDianZhaoDianPaiTypeNameInfo> getheigthlevelList(String street) {
        String sql="select heigthlevel as name,count(1) as count from hp_dianzhaodianpai_info  where street=? group by heigthlevel";
        return getEntryList(sql,new HpDianZhaoDianPaiTypeNameInfoRowMapper(),new Object[]{street});
    }

    @Override
    public List<HpDianZhaoDianPaiTypeNameInfo> getxingshiList(String street) {
        String sql="select xingshi as name,count(1) as count from hp_dianzhaodianpai_info   where street=? group by xingshi";
        return getEntryList(sql,new HpDianZhaoDianPaiTypeNameInfoRowMapper(),new Object[]{street});
    }

    @Override
    public List<HpDianZhaoDianPaiTypeNameInfo> getlevelList(String street) {
        String sql="select level as name,count(1) as count from hp_dianzhaodianpai_info  where street=? group by level";
        return getEntryList(sql,new HpDianZhaoDianPaiTypeNameInfoRowMapper(),new Object[]{street});
    }

    @Override
    public List<SysBuildingInfoWall> listSysBuildingInfoWall() {
        String sql="select * from sys_building_info_wall";
        return getEntryList(sql,new SysBuildingInfoWallRowMapper(),new Object[]{});
    }

    @Override
    public Integer listShiGongWallByStreet() {
        String sql="select count(1) from sys_building_info_wall";
        return queryInteger(sql,new Object[]{});
    }

    @Override
    public List<HpDianZhaoDianPaiTypeNameInfo> getDZDPSizeListBytravelname(String travelname) {
        String sql="select size as name,count(1) as count from hp_dianzhaodianpai_info where travelname=? group by size ";
        return getEntryList(sql,new HpDianZhaoDianPaiTypeNameInfoRowMapper(),new Object[]{travelname});
    }

    @Override
    public List<HpDianZhaoDianPaiTypeNameInfo> getheigthlevelListBytravelname(String travelname) {
        String sql="select heigthlevel as name,count(1) as count from hp_dianzhaodianpai_info  where travelname=? group by heigthlevel";
        return getEntryList(sql,new HpDianZhaoDianPaiTypeNameInfoRowMapper(),new Object[]{travelname});
    }

    @Override
    public List<HpDianZhaoDianPaiTypeNameInfo> getxingshiListBytravelname(String travelname) {
        String sql="select xingshi as name,count(1) as count from hp_dianzhaodianpai_info   where travelname=? group by xingshi";
        return getEntryList(sql,new HpDianZhaoDianPaiTypeNameInfoRowMapper(),new Object[]{travelname});
    }

    @Override
    public List<HpDianZhaoDianPaiTypeNameInfo> getlevelListBytravelname(String travelname) {
        String sql="select level as name,count(1) as count from hp_dianzhaodianpai_info  where travelname=? group by level";
        return getEntryList(sql,new HpDianZhaoDianPaiTypeNameInfoRowMapper(),new Object[]{travelname});
    }

    @Override
    public List<SysBuildingInfoStreet> listSysBuildingInfoStreetType(String type) {
        String sql="select * from sys_building_info_street where remark=?";
        return getEntryList(sql,new SysBuildingInfoStreetRowMapper(),new Object[]{type});
    }

    @Override
    public List<SysBuildingInfoStreet> listSysBuildingInfoStreetNet(String s) {
        String sql="select * from sys_building_info_street where reseau_id=?";
        return getEntryList(sql,new SysBuildingInfoStreetRowMapper(),new Object[]{s});
    }

    @Override
    public Double getAllCost() {
        String sql="select sum(cost) from sys_construction_project";
        return queryDouble(sql,new Object[]{});
    }

    @Override
    public Integer getAllCount() {
        String sql="select count(1) from sys_construction_project";
        return queryInteger(sql,new Object[]{});
    }

    @Override
    public Double getAllArea() {
        String sql="select sum(area) from sys_construction_project";
        return queryDouble(sql,new Object[]{});
    }

    @Override
    public Integer getAllTypeCount(String type) {
        String sql="select count(1) from sys_construction_project where status=?";
        return queryInteger(sql,new Object[]{type});
    }

    @Override
    public Integer getConstructionUnitCount() {
        String sql="select COUNT(DISTINCT construction_unit) from sys_construction_project";
        return queryInteger(sql,new Object[]{});
    }

    @Override
    public Integer getarchitectCount() {
        String sql="select COUNT(DISTINCT architect) from sys_construction_project";
        return queryInteger(sql,new Object[]{});
    }

    @Override
    public Integer getroadworkunitcount() {
        String sql="select COUNT(DISTINCT roadwork_unit) from sys_construction_project";
        return queryInteger(sql,new Object[]{});
    }

    @Override
    public Integer getsupervisingunitcount() {
        String sql="select COUNT(DISTINCT supervising_unit) from sys_construction_project";
        return queryInteger(sql,new Object[]{});
    }

    @Override
    public List<String> getListStreetInfo() {
        String sql="select street from sys_construction_project group by street";
        return queryStringArray(sql,new Object[]{});
    }

    @Override
    public List<SysHpXiaoFangAjType> getListStreetBuildInfo(String street) {
        String sql="select status as name,count(1) as count from sys_construction_project where street=? group by status";
        return getEntryList(sql,new SysHpXiaoFangAjTypeRowMapper(),new Object[]{street});
    }

    @Override
    public Integer gettower_crane_sumcount() {
        String sql="select sum(tower_crane_sum) from sys_construction_project where status='t' ";
        return queryInteger(sql,new Object[]{});
    }

    @Override
    public Integer getperson_ladder_sumcount() {
        String sql="select sum(person_ladder_sum) from sys_construction_project where status='t' ";
        return queryInteger(sql,new Object[]{});
    }

    @Override
    public Integer getnacelle_numcount() {
        String sql="select sum(nacelle_num) from sys_construction_project where status='t' ";
        return queryInteger(sql,new Object[]{});
    }

    @Override
    public List<SysHpXiaoFangAjType> getImageScheduleByDate() {
        String sql="select  image_schedule as name,count(1) as count  from sys_construction_project where status='t' group by image_schedule";
        return getEntryList(sql,new SysHpXiaoFangAjTypeRowMapper(),new Object[]{});
    }

    @Override
    public Integer getAllTypeCount1000() {
        String sql="select count(1) from sys_construction_project where status='z' and cost>1000";
        return queryInteger(sql,new Object[]{});
    }

    @Override
    public Integer getImportCount(String type) {
        String sql="select count(1) from sys_construction_project where status='z' and decoration_type=?";
        return queryInteger(sql,new Object[]{type});
    }

    @Override
    public List<SysConstructionProject> getListSysConstructionProjectByZS() {
        String sql="select  * from sys_construction_project where status='z'";
        return getEntryList(sql,new SysConstructionProjectRowMapper(),new Object[]{});
    }

    @Override
    public List<SysBuildingInfoStreet> listSysBuildingInfoStreetByKeyString(String keystring) {
        String sql="select * from sys_building_info_street where site_name like ? or site_street like ?";
        return getEntryList(sql,new SysBuildingInfoStreetRowMapper(),new Object[]{"%" + keystring + "%","%" + keystring + "%"});
    }

    @Override
    public List<SysConstructionProject> getListSysConstructionProjectByTJ() {
        String sql="select  * from sys_construction_project where status='t'";
        return getEntryList(sql,new SysConstructionProjectRowMapper(),new Object[]{});
    }

    @Override
    public List<SysConstructionProject> getListSysConstructionProjectByGkj() {
        String sql="select  * from sys_construction_project where status='g'";
        return getEntryList(sql,new SysConstructionProjectRowMapper(),new Object[]{});
    }

    @Override
    public SysConstructionProject getBuildInfoByProjectNameInfo(String projectname,String number) {
        String sql="select  * from sys_construction_project where number=? and project_name=?";
        return getEntry(sql,new SysConstructionProjectRowMapper(),new Object[]{projectname,number});
    }

    @Override
    public List<String> getListUnitNameByType(String type) {
        //0建设单位/1设计单位/2施工单位/3监理单位
        String sql="";
        if(type.equals("0")){
            sql="select DISTINCT construction_unit from sys_construction_project";
        }
        if(type.equals("1")){
            sql="select DISTINCT architect from sys_construction_project";
        }
        if(type.equals("2")){
            sql="select DISTINCT roadwork_unit from sys_construction_project";
        }
        if(type.equals("3")){
            sql="select DISTINCT supervising_unit from sys_construction_project";

        }
        return queryStringArray(sql,new Object[]{});
    }

    @Override
    public List<SysConstructionProject> getListSysConstructionProjectByName(String type, String name) {
        //0建设单位/1设计单位/2施工单位/3监理单位
        String sql="";
        if(type.equals("0")){
            sql="select * from sys_construction_project where construction_unit=?";
        }
        if(type.equals("1")){
            sql="select *  from sys_construction_project where architect=?";
        }
        if(type.equals("2")){
            sql="select * from sys_construction_project  where roadwork_unit=?";
        }
        if(type.equals("3")){
            sql="select *  from sys_construction_project where supervising_unit=?";

        }
        return getEntryList(sql,new SysConstructionProjectRowMapper(),new Object[]{name});
    }

    @Override
    public List<SysConstructionProjectVideo> getListSysConstructionProjectVideoByNumber(String number,String number1) {
        String sql="select  * from sys_construction_project_videos where number=? and project_name=? ";
        return getEntryList(sql,new SysConstructionProjectVideoRowMapper(),new Object[]{number,number1});
    }

    @Override
    public Integer getABC(String a) {
        String sql="select count(1) from hp_dianzhaodianpai_info  where id is not null and level =?  ";
        return queryInteger(sql,new Object[]{a});
    }

    @Override
    public Integer getHpDianZhaoDianPaiStreetAllCount() {
        String sql="select count(1) from hp_dianzhaodianpai_info";
        return queryInteger(sql,new Object[]{});
    }

    @Override
    public List<SysConstructionProjectVideo> getAllListSysConstructionProjectVideoByNumber() {
        String sql="select  * from sys_construction_project_videos ";
        return getEntryList(sql,new SysConstructionProjectVideoRowMapper(),new Object[]{});
    }


    @Override
    public List<HpDianZhaoDianPaiInfo> test() {
        String sql="select * from hp_dianzhaodianpai_info";
        return getEntryList(sql,new HpDianZhaoDianPaiInfoRowMapper(),new Object[]{});
    }


    /**
     * 批量更新测试代码
     * @param hpDianZhaoDianPaiInfoList
     */
    @Override
    public void branchUpdateTest(List<HpDianZhaoDianPaiInfo> hpDianZhaoDianPaiInfoList){
        String sql = "update hp_dianzhaodianpai_info_20210519 set wgs84Longitude = ?, wgs84Latitude = ? where id = ?";
        int[] updateCounts = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public int getBatchSize() {
                return hpDianZhaoDianPaiInfoList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            @Override
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                HpDianZhaoDianPaiInfo hpDianZhaoDianPaiInfo =  hpDianZhaoDianPaiInfoList.get(i);
                ps.setString(1, hpDianZhaoDianPaiInfo.getBaiduLongitude());
                ps.setString(2, hpDianZhaoDianPaiInfo.getBaiduLatitude());
                ps.setInt(3, hpDianZhaoDianPaiInfo.getId() );
            }
        });

        System.out.println("updateCounts = " + updateCounts.length);

    }


}
